using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public static partial class NewsInfoService
    {
        public static bool AddNewsInfo(NewsInfo newsInfo)
        {
            string sql =
                "INSERT NewsInfo (Title, NewsContent, Image, NewsDate, Tag, TypeID, XUHAO, TitleColor)" +
                "VALUES (@Title, @NewsContent, @Image, @NewsDate, @Tag, @TypeID, @XUHAO, @TitleColor)";

            sql += " ; SELECT @@IDENTITY";


            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@Title", newsInfo.Title),
				new SqlParameter("@NewsContent", newsInfo.NewsContent),
				new SqlParameter("@Image", newsInfo.Image),
				new SqlParameter("@NewsDate", newsInfo.NewsDate),
				new SqlParameter("@Tag", newsInfo.Tag),
				new SqlParameter("@TypeID", newsInfo.TypeID),
                new SqlParameter("@XUHAO", newsInfo.XUHAO),
                new SqlParameter("@TitleColor", newsInfo.TitleColor)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }


        public static bool DeleteNewsInfoByID(int iD)
        {
            string sql = "DELETE NewsInfo WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        public static bool ModifyNewsInfo(NewsInfo newsInfo)
        {
            string sql =
                "UPDATE NewsInfo " +
                "SET " +
                    "Title = @Title, " +
                    "NewsContent = @NewsContent, " +
                    "Image = @Image, " +
                    "NewsDate = @NewsDate, " +
                    "Tag = @Tag, " +
                    "TypeID = @TypeID, " +
                    "XUHAO = @XUHAO, " +
                    "TitleColor = @TitleColor " +
                "WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", newsInfo.ID),
				new SqlParameter("@Title", newsInfo.Title),
				new SqlParameter("@NewsContent", newsInfo.NewsContent),
				new SqlParameter("@Image", newsInfo.Image),
				new SqlParameter("@NewsDate", newsInfo.NewsDate),
				new SqlParameter("@Tag", newsInfo.Tag),
				new SqlParameter("@TypeID", newsInfo.TypeID),
                new SqlParameter("@XUHAO", newsInfo.XUHAO),
                new SqlParameter("@TitleColor", newsInfo.TitleColor)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static IList<NewsInfo> GetAllNewsInfos()
        {
            string sqlAll = "SELECT * FROM NewsInfo";
            return GetNewsInfosBySql(sqlAll);
        }

        public static int GetMaxXUEHAO()
        {
            string sqlAll = "SELECT top 1 XUHAO FROM NewsInfo order by XUHAO desc";
            int i = Convert.ToInt32(DBHelper.ExecuteScalar(CommandType.Text, sqlAll, null));
            return i;
        }


        public static int GetIDNewsInfosByXUEHAO(int xuehao)
        {
            string sqlAll = "SELECT ID FROM NewsInfo where XUHAO=@XUHAO";
            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@XUHAO", xuehao)
			};
            int i = Convert.ToInt32(DBHelper.ExecuteScalar(CommandType.Text, sqlAll, para));
            return i;
        }

        public static bool ModifyXUHANewsInfosByID(int pid, int xuhao)
        {
            string sql = "UPDATE NewsInfo SET XUHAO = @XUHAO WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", pid),
				new SqlParameter("@XUHAO", xuhao)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static IList<NewsInfo> GetNewsInfosLast()
        {
            string sqlAll = "SELECT top 6 * FROM NewsInfo order by XUHAO desc";
            return GetNewsInfosBySql(sqlAll);
        }

        public static IList<NewsInfo> GetNewsInfosByTypeID(int typeID)
        {
            string sqlAll = "SELECT * FROM NewsInfo where TypeID = @TypeID order by XUHAO desc";
            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@TypeID", typeID)
			};
            return GetNewsInfosBySql(sqlAll, para);
        }

        public static IList<NewsInfo> GetNewsInfosByTypeID(string typeID)
        {
            string sqlAll = "SELECT * FROM NewsInfo where TypeID in (" + typeID + ") order by XUHAO desc";
            return GetNewsInfosBySql(sqlAll);
        }

        public static IList<NewsInfo> GetNewsInfosByTypeIDIndex(string typeID)
        {
            string sqlAll = "SELECT top 10 * FROM NewsInfo where TypeID in (" + typeID + ") order by XUHAO desc";
            return GetNewsInfosBySql(sqlAll);
        }

        public static NewsInfo GetNewsInfoByID(int iD)
        {
            string sql = "SELECT * FROM NewsInfo WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            NewsInfo newsInfo = new NewsInfo();

            foreach (DataRow dr in temp.Rows)
            {
                newsInfo.ID = (int)dr["ID"];
                newsInfo.Title = (string)dr["Title"];
                newsInfo.NewsContent = (string)dr["NewsContent"];
                newsInfo.Image = (string)dr["Image"];
                newsInfo.NewsDate = (DateTime)dr["NewsDate"];
                newsInfo.Tag = (string)dr["Tag"];
                newsInfo.TypeID = (int)dr["TypeID"];
                newsInfo.XUHAO = (int)dr["XUHAO"];
                newsInfo.TitleColor = (string)dr["TitleColor"];

            }
            return newsInfo;

        }


        private static IList<NewsInfo> GetNewsInfosBySql(string safeSql)
        {
            List<NewsInfo> list = new List<NewsInfo>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, null))
            {
                while (dr.Read())
                {
                    NewsInfo newsInfo = new NewsInfo();

                    newsInfo.ID = (int)dr["ID"];
                    newsInfo.Title = (string)dr["Title"];
                    newsInfo.NewsContent = (string)dr["NewsContent"];
                    newsInfo.Image = (string)dr["Image"];
                    newsInfo.NewsDate = (DateTime)dr["NewsDate"];
                    newsInfo.Tag = (string)dr["Tag"];
                    newsInfo.TypeID = (int)dr["TypeID"];
                    newsInfo.XUHAO = (int)dr["XUHAO"];
                    newsInfo.TitleColor = (string)dr["TitleColor"];

                    list.Add(newsInfo);
                }
            }
            return list;
        }

        private static IList<NewsInfo> GetNewsInfosBySql(string safeSql, params SqlParameter[] para)
        {
            List<NewsInfo> list = new List<NewsInfo>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, para))
            {
                while (dr.Read())
                {
                    NewsInfo newsInfo = new NewsInfo();

                    newsInfo.ID = (int)dr["ID"];
                    newsInfo.Title = (string)dr["Title"];
                    newsInfo.NewsContent = (string)dr["NewsContent"];
                    newsInfo.Image = (string)dr["Image"];
                    newsInfo.NewsDate = (DateTime)dr["NewsDate"];
                    newsInfo.Tag = (string)dr["Tag"];
                    newsInfo.TypeID = (int)dr["TypeID"];
                    newsInfo.XUHAO = (int)dr["XUHAO"];
                    newsInfo.TitleColor = (string)dr["TitleColor"];

                    list.Add(newsInfo);
                }
            }
            return list;
        }
    }
}
